<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>数据库基础练习</title>
</head>
<body>
    <div>
        <p>show databases;</p>
        <p>use thirdweek;</p>
        <p>select database();</p>
        <p>alter database thirdweek charset=utf8;</p>

        create table user(<br>
        <p style="text-indent: 2em">id int primary key auto_increment comment '序号',</p>
        <p style="text-indent: 2em">username varchar(16) not null unique comment '用户名',</p>
        <p style="text-indent: 2em">password varchar(16) not null comment '密码',</p>
        <p style="text-indent: 2em">gender tinyint default 0 comment '性别',</p>
        <p style="text-indent: 2em">account decimal(12,2) default 0 comment '账户余额',</p>
        <p style="text-indent: 2em">vip boolean default FALSE comment 'vip'</p>
        );

        <p>insert into user(id, username, password, gender, account, vip) value (1,'zhangsan','000000',1,526843.36,TRUE);</p>
        insert into user(id, username, password, gender, account, vip) values
        <p style="text-indent: 2em">(2,'lisi','000000',1,9089.265,FALSE),</p>
        <p style="text-indent: 2em">(3,'wanghui','000000',0,100987.087,TRUE);</p>

        <p>update user set username='update_test' where id=1;</p>

        <p>delete from user where id=2;</p>
        <p>delete from user where id<=3;</p>
        <p>select * from user;</p>

        <p>alter table user add telephone varchar(11) unique comment '手机号码';</p>
        <p>alter table user change telephone tel varchar(11) unique comment '手机号码';</p>
        <p>alter table user drop tel;</p>

        <p>rename table user to tb_user;</p>

        <p>drop table tb_user;</p>
        <p>truncate tb_user;</p>



        create table tbl(
        <p style="text-indent: 2em">id int primary key auto_increment,</p>
        <p style="text-indent: 2em">username varchar(16) not null unique,</p>
        <p style="text-indent: 2em">password varchar(16) not null,</p>
        <p style="text-indent: 2em">createDatetime date</p>
        );<br>

        insert into tbl (username,password,createDatetime) values
        <p style="text-indent: 2em">("张伟","pass1","2019-07-11"),</p>
        <p style="text-indent: 2em">("王伟","pass2","2019-07-11"),</p>
        <p style="text-indent: 2em">("王芳","pass3","2019-07-12"),</p>
        <p style="text-indent: 2em">("李伟","pass4","2019-07-11"),</p>
        <p style="text-indent: 2em">("王秀英","pass5","2019-07-12"),</p>
        <p style="text-indent: 2em">("李秀英","pass6","2019-07-11"),</p>
        <p style="text-indent: 2em">("李娜","pass7","2019-07-10"),</p>
        <p style="text-indent: 2em">("张秀英","pass8","2019-07-10"),</p>
        <p style="text-indent: 2em">("刘伟","pass9","2019-07-12"),</p>
        <p style="text-indent: 2em">("张敏","pass10","2019-07-10");</p>

        <p>select * from tbl;</p>

        <p>select username,createDatetime from tbl;</p>

        <p>select username,password from tbl;</p>

        <p>select password from tbl where username='王伟';</p>

        <p>select * from tbl where createDatetime is not null;</p>

        <p>select createDatetime from tbl where id>=5;</p>

        <p>select * from tbl where username like '%秀%';</p>

        <p>select * from tbl where username like '王_';</p>

        <p>select * from tbl where username in ('王伟','李娜');</p>

        <p>select * from tbl where username like '李%' and id > 5;</p>
        <p>select * from tbl where username like '李%' or id > 8;</p>

        <p>select count(*) from tbl where createDatetime is not null group by createDatetime;</p>

        <p>select count(*) from tbl where createDatetime is not null group by createDatetime having count(*)>3;</p>

        <p>select * from tbl order by createDatetime desc,id asc;</p>

        <p>select * from tbl limit 3,5;</p>

        <p>create index in1 on tbl(username(10));</p>
        <p>drop index in1 on tbl;</p>

        create table tb1 (
        <p style="text-indent: 2em">id int primary key auto_increment,</p>
        <p style="text-indent: 2em">username varchar(16)</p>
        );<br>
        create table tb2(
        <p style="text-indent: 2em">id int primary key auto_increment,</p>
        <p style="text-indent: 2em">topic varchar(200),</p>
        <p style="text-indent: 2em">user_id int,</p>
        <p style="text-indent: 2em">constraint fu1 foreign key (user_id) references tb1(id) on delete CASCADE</p>
        );<br>
        create table tb3(
        <p style="text-indent: 2em">id int primary key auto_increment,</p>
        <p style="text-indent: 2em">user_id int,</p>
        <p style="text-indent: 2em">forum_id int,</p>
        <p style="text-indent: 2em">constraint c1 foreign key (user_id) references tb1(id) on delete cascade,</p>
        <p style="text-indent: 2em">constraint c2 foreign key (forum_id) references tb2(id) on delete cascade</p>
        );
    </div>
</body>
</html>